﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;

namespace LicPdaService
{
    public class PDAXmlSerialProcess
    {
        /// <summary>
        /// 4.0通过Serial/Master Label获取ASN明细 Add By Quinn.Ling 201703
        /// DataSource:Inventory
        /// </summary>

        public static string GetPDASerialData(XmlDocument doc)
        {
            #region 通用变量定义
            string docResult = string.Empty; //返回值
            string userid = string.Empty;
            string whseid = string.Empty;
            string serial = string.Empty;
            string asn = string.Empty;
            string sku = string.Empty;
            string qty = string.Empty;
            string return_status = "";
            List<string> sqllists = new List<string>();
            string strSql = string.Empty;

            XmlDocument docreturn = new XmlDocument();//返回报文
            XmlDeclaration dec = docreturn.CreateXmlDeclaration("1.0", "utf-8", null);//建立Xml的定义声明  
            docreturn.AppendChild(dec);
            XmlElement rootResult = docreturn.CreateElement("NewDataSet"); //创建根节点
            #endregion

            try
            {
                #region 参数源
                //App.ws = new Fcl.WSOP.sqldbop();
                //App.ws.Init(System.Configuration.ConfigurationManager.ConnectionStrings["adpwmsConnectionString"].ConnectionString);
                XmlNode root = doc.DocumentElement; // 报文根节点
                XmlNode nodeSku = root.SelectSingleNode("asn");
                XmlNodeList xmlnodelist = nodeSku.ChildNodes;

                userid = nodeSku.SelectSingleNode("userid").InnerText;
                whseid = nodeSku.SelectSingleNode("whseid").InnerText;
                serial = nodeSku.SelectSingleNode("serial").InnerText;
                string[] ls;
                ls = serial.Split('|');

                if (ls.Length == 5)
                {
                    //通过Master Label查询
                    strSql = "select receiptkey,sku,pre_qty from ReceiptDetail where status in ('01','02','03','04','05') and sku='" + ls[0].ToString() + "'   and whseid='"+whseid+"'";
                }
                else if (ls.Length == 4)
                {
                    strSql = "select receiptkey,sku,pre_qty from ReceiptDetail where status in ('01','02','03','04','05') and sku='" + ls[0].ToString() + "'  and whseid='" + whseid + "'";
                }
                else
                {
                    ////通过Serial查询，逻辑如下:
                    //1、Serial号对应唯一ASN时，获取此唯一的ASN
                    //2、Serail号对应多笔ASN时，执行如下判定逻辑
                    //    2.1、存在Excel导入时----只抓取Excel导入的数据，不区分此ASN是否已上架
                    //    2.2、不存在Excel导入时--- 优先抓取未上架的CMS抓取过来的ASN数据，若已全部上架时随机抓取一个ASN
                    //    2.3、存在两笔以上Excel导入时---优先抓取未上架的手工ASN数据，若已全部上架时随机抓取一个手工ASN

                    #region old逻辑-取消执行
                      //strSql = "SELECT D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') AND D.status IN ('01','02','03','04','05')";
                      //DataTable dtSerail = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                      //if (dtSerail.Rows.Count > 1)
                      //{
                      //    strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and  D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') and LTRIM(RTRIM(data_from)) !='CMS' AND D.status IN ('01','02','03','04','05')";
                      //    DataTable dtSerail_cms= App.GetDataTable(App.DBconWMS, strSql, "Sku");
                      //    if (dtSerail_cms.Rows.Count <= 0)
                      //    {
                      //        strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "'  and D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') AND D.status IN ('01','02','03','04','05')";
                      //    }
                      //}
                    #endregion

                    strSql = "SELECT D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06')";
                    DataTable dtSerail = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                    if (dtSerail.Rows.Count > 1)
                    {
                        strSql = "SELECT  D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and  D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') and LTRIM(RTRIM(data_from)) !='CMS'";
                        DataTable dtSerail_cms = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                        if (dtSerail_cms.Rows.Count <= 0)  //未发现非CMS抓取的数据
                        {
                            strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "'  and D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') AND D.status IN ('01','02','03','04','05')";
                            DataTable dtSerail_cmx = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                            if (dtSerail_cmx.Rows.Count <= 0)
                            {
                                strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "'  and D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06')";
                            }
                        }
                        else if (dtSerail_cms.Rows.Count > 1) //包含两笔以上非CMS抓取的数据
                        {
                            strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and  D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') and LTRIM(RTRIM(data_from)) !='CMS' AND D.status IN ('01','02','03','04','05')";
                            DataTable dtSerail_cmy = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                            if (dtSerail_cmy.Rows.Count <= 0)
                            {
                                strSql = "SELECT TOP 1 D.receiptkey,sku,pre_qty  FROM ReceiptDetail AS D LEFT JOIN Receipt AS H ON D.whseid=H.whseid AND D.receiptkey=H.receiptkey WHERE  D.udf1='" + serial + "' and  D.whseid='" + whseid + "' AND H.status IN ('01','02','03','04','05','06') and LTRIM(RTRIM(data_from)) !='CMS'";
                            }

                        }
                    }
                }

                DataTable dt = App.GetDataTable(App.DBconWMS, strSql, "Sku");
                #endregion

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        #region
                        asn = dt.Rows[i]["receiptkey"].ToString();
                        sku = dt.Rows[i]["sku"].ToString();
                        qty = dt.Rows[i]["pre_qty"].ToString();
                        #endregion

                        #region 构建XML报文
                        XmlNode asn_result = docreturn.CreateElement("asn_return");

                        XmlElement useridResult = docreturn.CreateElement("userid");
                        useridResult.InnerText = userid;
                        asn_result.AppendChild(useridResult);

                        XmlElement whseidResult = docreturn.CreateElement("whseid");
                        whseidResult.InnerText = whseid;
                        asn_result.AppendChild(whseidResult);

                        XmlElement serialResult = docreturn.CreateElement("serial");
                        serialResult.InnerText = serial;
                        asn_result.AppendChild(serialResult);

                        XmlElement asnResult = docreturn.CreateElement("asn");
                        asnResult.InnerText = asn;
                        asn_result.AppendChild(asnResult);

                        XmlElement skuResult = docreturn.CreateElement("sku");
                        skuResult.InnerText = sku;
                        asn_result.AppendChild(skuResult);

                        XmlElement qtyResult = docreturn.CreateElement("qty");
                        qtyResult.InnerText = qty;
                        asn_result.AppendChild(qtyResult);

                        XmlElement return_statusResult = docreturn.CreateElement("return_status");
                        return_statusResult.InnerText = return_status;
                        asn_result.AppendChild(return_statusResult);

                        rootResult.AppendChild(asn_result);
                        #endregion
                    }
                }
                else
                {
                    #region 构建XML报文
                    return_status = "获取ASN失败！！！ 可能原因： 1、扫描的 料号或Serail 无对应的ASN预报  2、对应的ASN已作废或已上架完毕 3、PDA登录厂别错误  4、CMS数据还没有同步至WMS(可等待10分钟后再度确认)";

                    XmlNode asn_result = docreturn.CreateElement("asn_return");

                    XmlElement useridResult = docreturn.CreateElement("userid");
                    useridResult.InnerText = userid;
                    asn_result.AppendChild(useridResult);

                    XmlElement whseidResult = docreturn.CreateElement("whseid");
                    whseidResult.InnerText = whseid;
                    asn_result.AppendChild(whseidResult);

                    XmlElement serialResult = docreturn.CreateElement("serial");
                    serialResult.InnerText = serial;
                    asn_result.AppendChild(serialResult);

                    XmlElement asnResult = docreturn.CreateElement("asn");
                    asnResult.InnerText = asn;
                    asn_result.AppendChild(asnResult);

                    XmlElement skuResult = docreturn.CreateElement("sku");
                    skuResult.InnerText = sku;
                    asn_result.AppendChild(skuResult);

                    XmlElement qtyResult = docreturn.CreateElement("qty");
                    qtyResult.InnerText = qty;
                    asn_result.AppendChild(qtyResult);

                    XmlElement return_statusResult = docreturn.CreateElement("return_status");
                    return_statusResult.InnerText = return_status;
                    asn_result.AppendChild(return_statusResult);

                    rootResult.AppendChild(asn_result);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                App.Log("PDAXMLSerialProcess.cs-1", ex.Message.ToString());
                //throw new Exception("异常源：PDAXmlPrePickProcess:GetPDAPrePickData;" + "  " + ex.Message);
            }
            docreturn.AppendChild(rootResult);
            return docreturn.InnerXml;
        }
    }
}
